/**
 * 数据库 客户端（基础设施层）
 */
const mysql = require('mysql2/promise');
const { getLogger } = require('../logger');
const logger = getLogger('infra.dbClient');
const config = require('../../config');

const dbCfg = config.db || {};

let dbPool = null;
let isConnected = false;

const initializeDbPool = () => {
  if (!dbPool) {
    // 分离MySQL2有效的连接池选项和自定义选项
    // MySQL2 有效的连接池选项
    const mysql2ValidOptions = {
      host: dbCfg.host,
      user: dbCfg.user,
      password: dbCfg.password,
      database: dbCfg.database,
      port: dbCfg.port,
      connectionLimit: dbCfg.connectionLimit,
      // 启用连接保持活动状态
      enableKeepAlive: dbCfg.enableKeepAlive
    };

    // 创建连接池时只传递有效的MySQL2选项
    dbPool = mysql.createPool(mysql2ValidOptions);
    
    dbPool.on('error', (err) => {
      isConnected = false;
      logger.error('❌ 数据库连接错误:', {
        error: err.message,
        code: err.code,
        errno: err.errno,
        sqlState: err.sqlState
      });
    });
    
    process.on('SIGINT', async () => {
      try {
        await dbPool.end();
        logger.info('🛑 数据库连接池已关闭');
      } catch (error) {
        logger.error('❌ 关闭数据库连接池时出错:', error);
      }
    });
    
    // 记录自定义配置以供应用程序使用
    logger.debug('📋 自定义数据库配置:', {
      connectionTimeout: dbCfg.connectionTimeout,
      queryTimeout: dbCfg.queryTimeout,
      enableHealthCheck: dbCfg.enableHealthCheck,
      healthCheckInterval: dbCfg.healthCheckInterval,
      keepAliveInitialDelay: dbCfg.keepAliveInitialDelay
    });
  }
  return dbPool;
};

const getDbPool = () => {
  if (!dbPool) {
    initializeDbPool();
  }
  return dbPool;
};

const isDatabaseConnected = () => isConnected;

async function checkDatabaseConnection() {
  try {
    // 确保连接池已初始化（已包含有效选项过滤）
    const db = getDbPool();
    if (isConnected) {
      logger.debug('🔄 数据库已连接，执行验证检查...');
    } else {
      logger.info('🔍 正在检测数据库连接...');
    }
    // 只使用有效的MySQL2连接选项进行记录
    const validDbCfg = {
      host: dbCfg.host,
      user: dbCfg.user,
      database: dbCfg.database,
      connectionLimit: dbCfg.connectionLimit,
      port: dbCfg.port
    };
    if (!isConnected) {
      logger.info('📋 数据库配置信息（有效选项）:', validDbCfg);
    }
    let connection;
    try {
      // 直接使用连接池执行查询，避免可能的无效选项传递
      const [testResult] = await db.execute('SELECT 1 as test');
      const previousState = isConnected;
      isConnected = true;
      if (!previousState) {
        logger.info('✅ 数据库连接成功');
        const [versionRows] = await db.execute('SELECT VERSION() as version');
        logger.info('📊 数据库版本:', versionRows[0].version);
        const [dbRows] = await db.execute('SELECT DATABASE() as current_db');
        logger.info('💾 当前数据库:', dbRows[0].current_db);
      } else {
        logger.debug('✅ 数据库连接验证通过');
      }
      return true;
    } finally {
      if (connection) connection.release();
    }
  } catch (error) {
    const previousState = isConnected;
    isConnected = false;
    if (previousState) {
      logger.warn('⚠️ 数据库连接状态变为断开');
    } else if (isConnected === false) {
      logger.error('❌ 数据库连接失败:', {
        error: error,
        code: error.code,
        errno: error.errno,
        sqlState: error.sqlState
      });
      if (error.code === 'ECONNREFUSED') {
        logger.error('🔧 解决建议: 检查MySQL服务是否启动，端口是否正确');
      } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
        logger.error('🔧 解决建议: 检查数据库用户名和密码是否正确');
      } else if (error.code === 'ER_BAD_DB_ERROR') {
        logger.error('🔧 解决建议: 检查数据库名称是否存在');
      } else if (error.code === 'ETIMEDOUT') {
        logger.error('🔧 解决建议: 检查网络连接和数据库服务器地址');
      }
    }
    return false;
  }
}

async function closeDbPool() {
  try {
    if (dbPool) {
      await dbPool.end();
      logger.info('🛑 数据库连接池已关闭');
    }
  } catch (error) {
    logger.error('❌ 关闭数据库连接池时出错:',{}, error);
  }
}

module.exports = {
  initializeDbPool,
  getDbPool,
  isDatabaseConnected,
  checkDatabaseConnection,
  closeDbPool
};